ALTER PROCEDURE [dbo].[Create_new_scenario] @user_id         INT, 
                                            @file_name       VARCHAR(200), 
                                            @file_desc       VARCHAR (200), 
                                            @default_file_No INT, 
                                            @new_file_No     INT output 
AS 
  BEGIN 
      IF @default_file_No NOT IN (SELECT file_no 
                                  FROM   dbo.dvip_sys_files) 
        BEGIN 
            SET @default_file_No = (SELECT file_no 
                                    FROM   dbo.dvip_sys_default_file) 
        END 

      SET @new_file_No = (SELECT Isnull(Max(file_no), 100) + 1 
                          FROM   dbo.dvip_sys_files) 

      INSERT INTO dbo.dvip_sys_files 
                  (file_no, 
                   user_id, 
                   file_name, 
                   file_desc, 
                   created_time, 
                   analyzed) 
      VALUES      (@new_file_No, 
                   @user_id, 
                   @file_name, 
                   @file_desc, 
                   CURRENT_TIMESTAMP, 
                   0) 

      INSERT INTO dbo.dvip_in_bodyweight_open 
                  (file_no, 
                   lact, 
                   abs_adjustment, 
                   pct_adjustment, 
                   mim1, 
                   mim2, 
                   mim3, 
                   mim4, 
                   mim5, 
                   mim6, 
                   mim7, 
                   mim8, 
                   mim9, 
                   mim10, 
                   mim11, 
                   mim12, 
                   mim13, 
                   mim14, 
                   mim15, 
                   mim16, 
                   mim17, 
                   mim18, 
                   mim19, 
                   mim20, 
                   mim21, 
                   mim22, 
                   mim23, 
                   mim24) 
      SELECT @new_file_No, 
             lact, 
             abs_adjustment, 
             pct_adjustment, 
             mim1, 
             mim2, 
             mim3, 
             mim4, 
             mim5, 
             mim6, 
             mim7, 
             mim8, 
             mim9, 
             mim10, 
             mim11, 
             mim12, 
             mim13, 
             mim14, 
             mim15, 
             mim16, 
             mim17, 
             mim18, 
             mim19, 
             mim20, 
             mim21, 
             mim22, 
             mim23, 
             mim24 
      FROM   dbo.dvipv_bodyweight_open 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_cull_risk_lact 
                  (file_no, 
                   lact1, 
                   lact2, 
                   lact3, 
                   lact4, 
                   lact5, 
                   lact6, 
                   lact7, 
                   lact8, 
                   lact9, 
                   lact10, 
                   lact11, 
                   lact12) 
      SELECT @new_file_No, 
             lact1, 
             lact2, 
             lact3, 
             lact4, 
             lact5, 
             lact6, 
             lact7, 
             lact8, 
             lact9, 
             lact10, 
             lact11, 
             lact12 
      FROM   dbo.dvipv_cull_risk_lact 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_cull_risk_mim 
                  (file_no, 
                   mim1, 
                   mim2, 
                   mim3, 
                   mim4, 
                   mim5, 
                   mim6, 
                   mim7, 
                   mim8, 
                   mim9, 
                   mim10, 
                   mim11, 
                   mim12, 
                   mim13, 
                   mim14, 
                   mim15, 
                   mim16, 
                   mim17, 
                   mim18, 
                   mim19, 
                   mim20, 
                   mim21, 
                   mim22, 
                   mim23, 
                   mim24) 
      SELECT @new_file_No, 
             mim1, 
             mim2, 
             mim3, 
             mim4, 
             mim5, 
             mim6, 
             mim7, 
             mim8, 
             mim9, 
             mim10, 
             mim11, 
             mim12, 
             mim13, 
             mim14, 
             mim15, 
             mim16, 
             mim17, 
             mim18, 
             mim19, 
             mim20, 
             mim21, 
             mim22, 
             mim23, 
             mim24 
      FROM   dbo.dvipv_cull_risk_mim 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_cull_seasonality 
                  (file_no, 
                   abs_adjustment, 
                   pct_adjustment, 
                   seasonality, 
                   jan, 
                   feb, 
                   mar, 
                   apr, 
                   may, 
                   jun, 
                   jul, 
                   aug, 
                   sep, 
                   oct, 
                   nov, 
                   dec) 
      SELECT @new_file_No, 
             abs_adjustment, 
             pct_adjustment, 
             seasonality, 
             jan, 
             feb, 
             mar, 
             apr, 
             may, 
             jun, 
             jul, 
             aug, 
             sep, 
             oct, 
             nov, 
             dec 
      FROM   dbo.dvipv_cull_seasonality 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_milk_seasonality 
                  (file_no, 
                   lact, 
                   abs_adjustment, 
                   pct_adjustment, 
                   seasonality, 
                   jan, 
                   feb, 
                   mar, 
                   apr, 
                   may, 
                   jun, 
                   jul, 
                   aug, 
                   sep, 
                   oct, 
                   nov, 
                   dec) 
      SELECT @new_file_No, 
             lact, 
             abs_adjustment, 
             pct_adjustment, 
             seasonality, 
             jan, 
             feb, 
             mar, 
             apr, 
             may, 
             jun, 
             jul, 
             aug, 
             sep, 
             oct, 
             nov, 
             dec 
      FROM   dbo.dvipv_milk_seasonality 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_milk_wood_curve 
                  (file_no, 
                   lact1a, 
                   lact1b, 
                   lact1c, 
                   lact2a, 
                   lact2b, 
                   lact2c, 
                   lact3a, 
                   lact3b, 
                   lact3c) 
      SELECT @new_file_No, 
             lact1a, 
             lact1b, 
             lact1c, 
             lact2a, 
             lact2b, 
             lact2c, 
             lact3a, 
             lact3b, 
             lact3c 
      FROM   dbo.dvipv_milk_wood_curve 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_misc 
                  (file_no, 
                   unit, 
                   analysis, 
                   write_pol, 
                   write_rpo, 
                   start_sea, 
                   policy, 
                   max_mim, 
                   cull_milk, 
                   cull_margin, 
                   interest, 
                   p_loss, 
                   inv_cull_pct, 
                   funk, 
                   p_calf, 
                   p_fixed_labor, 
                   p_fixed_other, 
                   p_other_dry, 
                   p_other_lact, 
                   p_tmr_dm_dry, 
                   p_tmr_dm_lac, 
                   dmi_dry, 
                   pct_fat, 
                   pa_cv, 
                   milk_repeat, 
                   p_labor, 
                   time_fresh, 
                   time_lact, 
                   time_dry, 
                   vet_cost_1, 
                   vet_cost_2, 
                   vet_cost_3, 
                   vet_cost_4) 
      SELECT @new_file_No, 
             unit, 
             analysis, 
             write_pol, 
             write_rpo, 
             start_sea, 
             policy, 
             max_mim, 
             cull_milk, 
             cull_margin, 
             interest, 
             p_loss, 
             inv_cull_pct, 
             funk, 
             p_calf, 
             p_fixed_labor, 
             p_fixed_other, 
             p_other_dry, 
             p_other_lact, 
             p_tmr_dm_dry, 
             p_tmr_dm_lac, 
             dmi_dry, 
             pct_fat, 
             pa_cv, 
             milk_repeat, 
             p_labor, 
             time_fresh, 
             time_lact, 
             time_dry, 
             vet_cost_1, 
             vet_cost_2, 
             vet_cost_3, 
             vet_cost_4 
      FROM   dbo.dvipv_misc 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_price 
                  (file_no, 
                   year, 
                   month, 
                   breed_cows, 
                   enter_heifer, 
                   heifer_price, 
                   cull_price, 
                   milk_price) 
      SELECT @new_file_No, 
             year, 
             month, 
             breed_cows, 
             enter_heifer, 
             heifer_price, 
             cull_price, 
             milk_price 
      FROM   dbo.dvipv_price 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_abort_risk_mpreg 
                  (file_no, 
                   preg_month2, 
                   preg_month3, 
                   preg_month4, 
                   preg_month5, 
                   preg_month6, 
                   preg_month7, 
                   preg_month8) 
      SELECT @new_file_No, 
             preg_month2, 
             preg_month3, 
             preg_month4, 
             preg_month5, 
             preg_month6, 
             preg_month7, 
             preg_month8 
      FROM   dbo.dvipv_repro_abort_risk_mpreg 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_conc_pct_mim 
                  (file_no, 
                   mim3, 
                   mim4, 
                   mim5, 
                   mim6, 
                   mim7, 
                   mim8, 
                   mim9, 
                   mim10, 
                   mim11, 
                   mim12, 
                   mim13, 
                   mim14, 
                   mim15, 
                   mim16) 
      SELECT @new_file_No, 
             mim3, 
             mim4, 
             mim5, 
             mim6, 
             mim7, 
             mim8, 
             mim9, 
             mim10, 
             mim11, 
             mim12, 
             mim13, 
             mim14, 
             mim15, 
             mim16 
      FROM   dbo.dvipv_repro_conc_pct_mim 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_conc_risk_lact 
                  (file_no, 
                   lact1, 
                   lact2, 
                   lact3, 
                   lact4, 
                   lact5, 
                   lact6, 
                   lact7, 
                   lact8, 
                   lact9, 
                   lact10, 
                   lact11, 
                   lact12) 
      SELECT @new_file_No, 
             lact1, 
             lact2, 
             lact3, 
             lact4, 
             lact5, 
             lact6, 
             lact7, 
             lact8, 
             lact9, 
             lact10, 
             lact11, 
             lact12 
      FROM   dbo.dvipv_repro_conc_risk_lact 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_conc_seasonality 
                  (file_no, 
                   abs_adjustment, 
                   seasonality, 
                   jan, 
                   feb, 
                   mar, 
                   apr, 
                   may, 
                   jun, 
                   jul, 
                   aug, 
                   sep, 
                   oct, 
                   nov, 
                   dec) 
      SELECT @new_file_No, 
             abs_adjustment, 
             seasonality, 
             jan, 
             feb, 
             mar, 
             apr, 
             may, 
             jun, 
             jul, 
             aug, 
             sep, 
             oct, 
             nov, 
             dec 
      FROM   dbo.dvipv_repro_conc_seasonality 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_heat_pct_mim 
                  (file_no, 
                   mim3, 
                   mim4, 
                   mim5, 
                   mim6, 
                   mim7, 
                   mim8, 
                   mim9, 
                   mim10, 
                   mim11, 
                   mim12, 
                   mim13, 
                   mim14, 
                   mim15, 
                   mim16) 
      SELECT @new_file_No, 
             mim3, 
             mim4, 
             mim5, 
             mim6, 
             mim7, 
             mim8, 
             mim9, 
             mim10, 
             mim11, 
             mim12, 
             mim13, 
             mim14, 
             mim15, 
             mim16 
      FROM   dbo.dvipv_repro_heat_pct_mim 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_heat_seasonality 
                  (file_no, 
                   abs_adjustment, 
                   seasonality, 
                   jan, 
                   feb, 
                   mar, 
                   apr, 
                   may, 
                   jun, 
                   jul, 
                   aug, 
                   sep, 
                   oct, 
                   nov, 
                   dec) 
      SELECT @new_file_No, 
             abs_adjustment, 
             seasonality, 
             jan, 
             feb, 
             mar, 
             apr, 
             may, 
             jun, 
             jul, 
             aug, 
             sep, 
             oct, 
             nov, 
             dec 
      FROM   dbo.dvipv_repro_heat_seasonality 
      WHERE  file_no = @default_file_No; 

      INSERT INTO dbo.dvip_in_repro_misc 
                  (file_no, 
                   vwp_1, 
                   vwp_2, 
                   vwp_3, 
                   lbm, 
                   timebreeding, 
                   p_semen, 
                   maxlact) 
      SELECT @new_file_No, 
             vwp_1, 
             vwp_2, 
             vwp_3, 
             lbm, 
             timebreeding, 
             p_semen, 
             maxlact 
      FROM   dbo.dvipv_repro_misc 
      WHERE  file_no = @default_file_No; 
  END 